﻿using Lamex.Base;
using Lamex.Library;
using Lamex.mIE.BLL;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.Spreadsheet;
using Lamex.Interface;
using System.IO;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;
using Lamex.Library.CommonClass;
using DevExpress.XtraEditors;
using Lamex.Common;
using System.Runtime.InteropServices;
using System.Diagnostics;

namespace Lamex.mIE.UI
{
    public partial class Frm_IE_Part_SizeParmList_Excel : frmBaseChild
    {
        DataTable dtProc_Type_Ctrl = new DataTable();
        Lamex.mIE.ucControls.ucShowPic ucShowPicList = null;
        Lamex.mIE.ucControls.ucShowPic_Ref ucShowPicList1 = null;

        static DataTable dtList = new DataTable();//定义全局清单列表       
        static DataTable dtDWGNO_List = new DataTable();//产品类型选择后获取图纸号
        bool FLAG = true;//定义参考图纸是否显示    
        int CellRows = 0;
        static DataTable dtSizeCache = Common.GetTable.GetTable_SizeList_CH();//尺寸缓存表
        public Frm_IE_Part_SizeParmList_Excel()
        {
            InitializeComponent();
        }
        public Frm_IE_Part_SizeParmList_Excel(DataTable dt)
        {
            InitializeComponent();
            dtProc_Type_Ctrl = dt;
        }
        public Frm_IE_Part_SizeParmList_Excel(string DWG_NO,string Product_Type)
        {
            InitializeComponent();
            txtDWG_NO.Text = DWG_NO;
            txtProductItem.Text = Product_Type;
        }
        private void Frm_IE_Part_SizeParmList_Excel_Load(object sender, EventArgs e)
        {
            try { this.InitButtons(); } catch { }//初始化标准功能，因要应用于RibbonForm，所以加入异常处理

            GetDWG_NOList();
            Init();
            InitProductItem(dtProc_Type_Ctrl);
            InitDWGItem();
            // this.txtDWG_NO.Properties.Items.Clear();
            //SetExcelFormat();
            //自定义调整控件大小
            Lamex.Library.CommonClass.MoveControl m = new MoveControl(gc_CurrentPart);
            Lamex.Library.CommonClass.MoveControl m1 = new MoveControl(gc_ReferencePart);
        }

        private void Frm_IE_Part_SizeParmList_Excel_Resize(object sender, EventArgs e)
        {
            int Panel1_H = this.panelControl1.Height;
            int form_width = this.Width;
            int form_heigh = this.Height;
            panelControl2.Width = Convert.ToInt32(Math.Round(Convert.ToDouble(Convert.ToDouble(form_width) / 10), 1)) * 4;
            panelControl2.Height = form_heigh - Panel1_H;
            panelControl3.Width = Convert.ToInt32(Math.Round(Convert.ToDouble(Convert.ToDouble(form_width) / 10), 1)) * 6;
            panelControl3.Height = form_heigh - Panel1_H;

            //int P2_X = panelControl2.Location.X;
            //int P2_Y = panelControl2.Location.Y;
            //panelControl3.Location = new System.Drawing.Point(panelControl2.Width, P2_Y);

            SetSize();
        }
        public void SetSize()
        {
            int wight = this.panelControl3.Width;
            if (FLAG)
            {
                gc_CurrentPart.Width = wight / 2;
                gc_ReferencePart.Width = wight / 2;
            }
            else
            {
                gc_CurrentPart.Width = wight;
            }
        }
        public void Init()
        {
            // txtDWG_NO.Text = "";
            //txtPart_No.Text = "";
            txtProduct_Desc.Text = "";
            txtProduct_Desc.UcReadOnly = true;
            txtModelNo.Text = "";
            txtModelNo.UcReadOnly = true;
            gc_CurrentPart.Controls.Clear();
            gc_ReferencePart.Controls.Clear();
            gridControl1.DataSource = new DataTable();
            gridControl2.DataSource = new DataTable();
            SetSize();
            gc_CurrentPart.Visible = true;
            gc_ReferencePart.Visible = true;
        }
       
        //产品类型初始化
        public void InitProductItem(DataTable dt)
        {
            if (dt.Rows.Count > 0)
            {
                this.txtProductItem.Properties.Items.Clear();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    this.txtProductItem.Properties.Items.Add(dt.Rows[i]["PROD_CATEGORY"].ToString());
                }
                txtProductItem.SelectIndex = 0;
                txtProductItem.Properties.TextEditStyle = DevExpress.XtraEditors.Controls.TextEditStyles.DisableTextEditor;
            }
            else
            {
                dt = BaseFactory<IE_Part_TaskList>.Instance.GetProductItem();
                this.txtProductItem.Properties.Items.Clear();
                if (dt.Rows.Count > 0)
                {
                    string[] list = dt.Rows[0]["FIELD_VALUE_OPTION"].ToString().Split(',');
                    foreach (var item in list)
                    {
                        this.txtProductItem.Properties.Items.Add(item);
                    }
                }
            }
        }
        //图纸号初始化
        public void InitDWGItem()
        {
            this.txtDWG_NO.Properties.Items.Clear();
            if (dtDWGNO_List.Rows.Count > 0)
            {
                for (int i = 0; i < dtDWGNO_List.Rows.Count; i++)
                {
                    this.txtDWG_NO.Properties.Items.Add(dtDWGNO_List.Rows[i][0].ToString());
                }
            }
        }
        //获取图纸号
        public void GetDWG_NOList()
        {
            dtDWGNO_List = Common.GetDWG_List.Dwg_List("IE_Part_TaskList T LEFT jOIN IE_Part_MasterData M ON T.PART_NO=M.PART_NO",
           "distinct T.DWG_NO",
           "  and ISNULL(T.PARM_FLAG,'')!='1' and ISNULL(T.UPLOAD_FLAG,'')!='1' ");
            //获取工艺路线表上载标识不为1的数据
            DataTable dtRouting1 = Common.GetDWG_List.Dwg_List(@"IE_Part_SizeParmList R JOIN IE_Part_TaskList T on R.DWG_NO=T.DWG_NO and R.PART_NO=T.PART_NO JOIN IE_Part_MasterData M ON M.PART_NO=T.PART_NO",
                "distinct T.DWG_NO",
                "  and ISNULL(T.PARM_FLAG,'')='1' and ISNULL(T.UPLOAD_FLAG,'')!='1' ");

            //合并临时表
            dtDWGNO_List.Merge(dtRouting1);
            //赋值给图纸编号下拉
            dtDWGNO_List = dtDWGNO_List.DefaultView.ToTable(true, "DWG_NO");
        }
        //产品类型选择事件
        private void txtProductItem_UcTextChanged(object sender, EventArgs e)
        {         
            Init();       
            GetSizeParmList();
           // InitDWGItem();
        }
        //图纸号选择事件
        private void txtDWG_NO_UcTextChanged(object sender, EventArgs e)
        {
            if (txtDWG_NO.Text.Length > 0)
                txtProductItem_UcTextChanged(null, null);
        }
        //sheet click
        private void spreadsheetControl1_Click(object sender, EventArgs e)
        {

            Worksheet sheet = this.spreadsheetControl1.ActiveWorksheet;
            Range r = sheet.SelectedCell;
            string cellvalue = r.Value.ToString().Trim();//获取点击单元格的值
            if (!string.IsNullOrEmpty(cellvalue) && sheet.SelectedCell.LeftColumnIndex == 0) //A列
            //  (cellvalue.Length == 8 || cellvalue.Length == 9 || cellvalue.Length == 10))//需要重复判断是否点击了A列
            {
                SetSize();
                gridControl1.DataSource = new DataTable();
                gridControl2.DataSource = new DataTable();
                gc_ReferencePart.Visible = true;
                gc_CurrentPart.Visible = true;
                gridControl1.Visible = false;
                gridControl2.Visible = false;
                FLAG = true;//参考图纸是否显示
                gc_ReferencePart.Controls.Clear();
                gc_CurrentPart.Controls.Clear();

                //部件描述
                string Part_Desc = dtList.Select("DWG_NO='" + cellvalue + "'").Length > 0 ?
                   dtList.Select("DWG_NO='" + cellvalue + "'").CopyToDataTable().Rows[0]["CN_DESC"].ToString() : "";
                txtProduct_Desc.Text = Part_Desc;

                //产品类型
                string Part_Model = dtList.Select("DWG_NO='" + cellvalue + "'").Length > 0 ?
                  dtList.Select("DWG_NO='" + cellvalue + "'").CopyToDataTable().Rows[0]["MODEL_NO"].ToString() : "";
                txtModelNo.Text = Part_Model;

                //得到参考图纸号码
                string REF_PART = dtList.Select("DWG_NO='" + cellvalue + "'").Length > 0 ?
                    dtList.Select("DWG_NO='" + cellvalue + "'").CopyToDataTable().Rows[0]["REF_DWG"].ToString() : "";


                //根据图纸号获取产品类型，因为不同的产品类型图纸路径不一致
                //string sql_ProductType = @" select distinct PROD_CATEGORY from [dbo].[IE_Part_TaskList] where DWG_NO='" + cellvalue + "'";
                //DataTable dt_ProductTemp = BaseBuildSql.GetDataTableBySql(sql_ProductType);
                //显示图片           
                var listPic = Common.GetShowPic.ShowPic_STD(txtProductItem.Text.Trim(), cellvalue, REF_PART, cellvalue);

                //标准图纸
                if (listPic.Item1.Count > 0)
                {
                    ucShowPicList = new Lamex.mIE.ucControls.ucShowPic();
                    ucShowPicList.LoadImageList(listPic.Item1);
                    ucShowPicList.Dock = DockStyle.Fill;
                    gc_CurrentPart.Controls.Add(ucShowPicList);
                }
                //参考图纸
                if (listPic.Item2.Count > 0)
                {
                    ucShowPicList1 = new Lamex.mIE.ucControls.ucShowPic_Ref();
                    ucShowPicList1.LoadImageList(listPic.Item2);
                    ucShowPicList1.Dock = DockStyle.Fill;
                    gc_ReferencePart.Controls.Add(ucShowPicList1);
                }
                else
                {
                    gc_ReferencePart.Visible = false;
                    int wight = this.panelControl3.Width;
                    gc_CurrentPart.Width = wight;
                    FLAG = false;
                }
            }
            if (!string.IsNullOrEmpty(cellvalue) && sheet.SelectedCell.LeftColumnIndex == 1) //B列
            {
                SetSize();
                gridControl1.Visible = true;
                gridControl2.Visible = true;
                gridControl1.Dock = DockStyle.Fill;
                gridControl2.Dock = DockStyle.Fill;
                gc_ReferencePart.Visible = true;
                gc_CurrentPart.Visible = true;
                FLAG = true;//参考图纸是否显示
                gc_ReferencePart.Controls.Clear();
                gc_CurrentPart.Controls.Clear();
                //产品类型
                string Part_Model = dtList.Select("PART_NO='" + cellvalue + "'").Length > 0 ?
                  dtList.Select("PART_NO='" + cellvalue + "'").CopyToDataTable().Rows[0]["MODEL_NO"].ToString() : "";
                txtModelNo.Text = Part_Model;

                DataTable dt = new DataTable();
                DataTable dt_Ref = new DataTable();
                string sql = "", sql_Ref = "";
                if (cellvalue.Substring(1, 2) == "96")
                {
                    sql = @"SELECT     
       [COMPONENT]
      ,[DESCRIBES]   
      ,[UNIT]
      ,[SINGLE_QTY]
      ,[DRAWING_NO]    
  FROM [LamexMESManager].[dbo].[B_IE_PACKAGE_BOM_DATA_SJR]
  where MATERIAL_NO='" + cellvalue + "'";
                }
                else
                {
                    sql = @"SELECT 
       [COMPONENT]
      ,[DESCRIBES]   
      ,[UNIT]
      ,[SINGLE_QTY]
      ,[DRAWING_NO] 
  FROM [LamexMESManager].[dbo].[B_IE_PACKAGE_BOM_DATA_STD]
where MATERIAL_NO='" + cellvalue + "' ";
                }
                //得到参考物料号
                string REF_MATNO = dtList.Select("PART_NO='" + cellvalue + "'").Length > 0 ?
                    dtList.Select("PART_NO='" + cellvalue + "'").CopyToDataTable().Rows[0]["REF_MAT"].ToString() : "";
                if (!string.IsNullOrEmpty(REF_MATNO.Trim()))
                {
                    if (REF_MATNO.Substring(1, 2) == "96")
                    {
                        sql_Ref = @"SELECT     
       [COMPONENT]
      ,[DESCRIBES]   
      ,[UNIT]
      ,[SINGLE_QTY]
      ,[DRAWING_NO]    
  FROM [LamexMESManager].[dbo].[B_IE_PACKAGE_BOM_DATA_SJR]
  where MATERIAL_NO='" + REF_MATNO + "'";
                    }
                    else
                    {
                        sql_Ref = @"SELECT 
       [COMPONENT]
      ,[DESCRIBES]   
      ,[UNIT]
      ,[SINGLE_QTY]
      ,[DRAWING_NO] 
  FROM [LamexMESManager].[dbo].[B_IE_PACKAGE_BOM_DATA_STD]
where MATERIAL_NO='" + REF_MATNO + "' ";
                    }
                    dt_Ref = BaseBuildSql.GetDataTableBySql(sql_Ref);
                }
                dt = BaseBuildSql.GetDataTableBySql(sql);
                gridControl1.DataSource = dt;
                gridControl2.DataSource = dt_Ref;
                //gridView1.BestFitColumns();
                gridView1.OptionsView.ColumnAutoWidth = false;
                gridView2.OptionsView.ColumnAutoWidth = false;
                this.gridView1.BestFitColumns();
                this.gridView2.BestFitColumns();
                gc_CurrentPart.Controls.Add(gridControl1);
                gc_ReferencePart.Controls.Add(gridControl2);
            }
        }
        //保存
        public override void DoSave(IButtonInfo sender)
        {
            try
            {
                #region 按钮权限           
                if (!BuildSqlHelper.IsExistList("IE_Part_SizeParmList", "LamexMESManager")) return;
                #endregion
                spreadsheetControl1.CloseCellEditor(DevExpress.XtraSpreadsheet.CellEditorEnterValueMode.ActiveCell);//强制关闭editTor建
                string StrUpdate_User = Lamex.Common.Loginer.CurrentUser.AccountName;//修改人
                string StrSave_Time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");//保存时间
                DataTable dtTemp = Common.GetTable.GetTable_SizeList();
                Worksheet sheet = this.spreadsheetControl1.ActiveWorksheet;
                Workbook workbook = new Workbook();
                int ColumsCount = sheet.Columns.LastUsedIndex;
                int RowCount = dtSizeCache.Rows.Count;


                for (int i = 1; i < RowCount + 1; i++)
                {

                    //插入数据库
                    DataRow dr = dtTemp.NewRow();
                    dr["DWG_NO"] = sheet.GetCellValue(0, i).ToString();
                    dr["PART_NO"] = sheet.GetCellValue(1, i).ToString();
                    dr["WIDTH_1"] = string.IsNullOrEmpty(sheet.GetCellValue(2, i).ToString()) ? null : sheet.GetCellValue(2, i).ToString();
                    dr["WIDTH_2"] = string.IsNullOrEmpty(sheet.GetCellValue(3, i).ToString()) ? null : sheet.GetCellValue(3, i).ToString();
                    dr["DEPTH_1"] = string.IsNullOrEmpty(sheet.GetCellValue(4, i).ToString()) ? null : sheet.GetCellValue(4, i).ToString();
                    dr["DEPTH_2"] = string.IsNullOrEmpty(sheet.GetCellValue(5, i).ToString()) ? null : sheet.GetCellValue(5, i).ToString();
                    dr["THICK"] = string.IsNullOrEmpty(sheet.GetCellValue(6, i).ToString()) ? null : sheet.GetCellValue(6, i).ToString();
                    dr["LENGTH"] = string.IsNullOrEmpty(sheet.GetCellValue(7, i).ToString()) ? null : sheet.GetCellValue(7, i).ToString();
                    dr["WIDTH"] = string.IsNullOrEmpty(sheet.GetCellValue(8, i).ToString()) ? null : sheet.GetCellValue(8, i).ToString();
                    dr["HEIGHT"] = string.IsNullOrEmpty(sheet.GetCellValue(9, i).ToString()) ? null : sheet.GetCellValue(9, i).ToString();
                    dr["DEPTH"] = string.IsNullOrEmpty(sheet.GetCellValue(10, i).ToString()) ? null : sheet.GetCellValue(10, i).ToString();
                    dr["UPDATE_TIME"] = StrSave_Time;
                    dr["UPDATE_USER"] = StrUpdate_User;                
                    dtTemp.Rows.Add(dr);
                }
                //删除所有未上传的数据
                if (dtTemp.Rows.Count <= 0)
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show("无数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                //判断同一个图纸号每行第一个值是否为空，空的话执行颜色变化
                for (int j = 2; j <= 10; j++)
                {

                    for (int k = 0; k < dtTemp.Rows.Count; k++)
                    {
                        if (dtTemp.Rows[k]["DWG_NO"].ToString() == "NIL")
                            continue;
                        if (k < dtTemp.Rows.Count - 1)
                        {
                            if (dtTemp.Rows[k]["DWG_NO"].ToString().Trim() == dtTemp.Rows[k + 1]["DWG_NO"].ToString().Trim())
                            {
                                //k行空，k+1行有值，k标记颜色
                                if (dtTemp.Rows[k][j].ToString().Trim().Length == 0 && dtTemp.Rows[k + 1][j].ToString().Trim().Length > 0)
                                {
                                    sheet.Cells[k+1, j].FillColor = Color.Yellow;
                                    DevExpress.XtraEditors.XtraMessageBox.Show("有未维护的尺寸参数！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    return;
                                }
                                //k+1行空，k行有值，k+1标记颜色
                                if (dtTemp.Rows[k][j].ToString().Trim().Length > 0 && dtTemp.Rows[k + 1][j].ToString().Trim().Length == 0)
                                {
                                    sheet.Cells[k+2, j].FillColor = Color.Yellow;
                                    DevExpress.XtraEditors.XtraMessageBox.Show("有未维护的尺寸参数！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    return;
                                }
                            }
                        }
                    }
                }
              
                //同一个画面，如果有不维护参数的行，则删除临时表，避免批量保存
                int count = dtTemp.Rows.Count;

                for (int i = count - 1; i >= 0; i--)//采用倒序
                {
                    //判断同一个图纸号每行第一个值是否为空，空的话执行颜色变化
                    // if (!string.IsNullOrEmpty(dtTemp.Rows[i][j].ToString().Trim()))//sheet.GetCellValue(j, 1).ToString()))
                    //if (i > 0)
                    //{
                    //    for (int j = 2; j <= 10; j++)
                    //    {
                    //        if (dtTemp.Rows[i]["DWG_NO"].ToString().Trim() == dtTemp.Rows[i - 1]["DWG_NO"].ToString().Trim())
                    //        {
                    //            if ((dtTemp.Rows[i][j].ToString().Trim().Length >= 0 && dtTemp.Rows[i - 1][j].ToString().Trim().Length <= 0)||
                    //                (dtTemp.Rows[i][j].ToString().Trim().Length <= 0 && dtTemp.Rows[i - 1][j].ToString().Trim().Length >= 0))
                    //            {
                    //                sheet.Cells[i + 1, j].FillColor = Color.Yellow;
                    //                DevExpress.XtraEditors.XtraMessageBox.Show("有未维护的尺寸参数！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    //                return;
                    //            }
                    //        }
                    //    }
                    //}

                    //如果每一行都为空，则不保存到临时表
                    if (dtTemp.Rows[i]["WIDTH_1"].ToString().Trim().Length <= 0 && dtTemp.Rows[i]["WIDTH_2"].ToString().Trim().Length <= 0 &&
                         dtTemp.Rows[i]["DEPTH_1"].ToString().Trim().Length <= 0 && dtTemp.Rows[i]["DEPTH_2"].ToString().Trim().Length <= 0 &&
                        dtTemp.Rows[i]["THICK"].ToString().Trim().Length <= 0 && dtTemp.Rows[i]["LENGTH"].ToString().Trim().Length <= 0 &&
                        dtTemp.Rows[i]["WIDTH"].ToString().Trim().Length <= 0 && dtTemp.Rows[i]["HEIGHT"].ToString().Trim().Length <= 0 &&
                         dtTemp.Rows[i]["DEPTH"].ToString().Trim().Length <= 0)
                        dtTemp.Rows.RemoveAt(i);

                }
                StringBuilder sbFilter1 = new StringBuilder();
                StringBuilder sbFilter2 = new StringBuilder();
                List<string> delete1 = new List<string>();
                List<string> delete2 = new List<string>();
                for (int i = 0; i < dtTemp.Rows.Count; i++)
                {
                    delete1.Add(dtTemp.Rows[i]["DWG_NO"].ToString());
                    delete2.Add(dtTemp.Rows[i]["PART_NO"].ToString());
                }
                sbFilter1.AppendFormat(@" and DWG_NO IN ('{0}')", string.Join("','", delete1.ToArray()));
                sbFilter2.AppendFormat(@" and PART_NO IN ('{0}')", string.Join("','", delete2.ToArray()));
                string sql_Size = @"select DWG_NO,PART_NO from IE_Part_SizeParmList where 1=1" + sbFilter1 + sbFilter2 + "";
              
                if (BaseBuildSql.GetDataTableBySql(sql_Size).Rows.Count > 0)
                    DevExpress.XtraEditors.XtraMessageBox.Show("当前图纸或部件号已经有部件维护过尺寸参数，强制保存将更改同图号所有部件数据，并无法恢复！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                string sqlDel1 = @"Delete  FROM IE_Part_SizeParmList where 1=1 " + sbFilter1 + sbFilter2 + " ";
                int n = BaseBuildSql.ExecuteNonQuery(sqlDel1);


                if (BaseBuildSql.ExcuteInsert(dtTemp, "IE_Part_SizeParmList"))
                {
                    //更新【IE_Part_TaskList】的“尺寸录入标识”
                    string UpdateSql = @"update IE_Part_TaskList set PARM_FLAG='1' where 1=1" + sbFilter1 + sbFilter2 + " and  ISNULL(UPLOAD_FLAG,'')!='1'";
                    BaseBuildSql.ExecuteNonQuery(UpdateSql);
                    txtDWG_NO.Text = "";//会触发查询
                    this.txtDWG_NO.Properties.Items.Clear();
                    Init();
                    InitDWGItem();
                    sheet.Clear(spreadsheetControl1.Document.Range["A1:K" + CellRows + 1 + ""]);
                    dtSizeCache = Common.GetTable.GetTable_SizeList_CH();
                    if (dtTemp.Rows.Count>0)
                    DevExpress.XtraEditors.XtraMessageBox.Show("保存成功,共【" + dtTemp.Rows.Count + "】条！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    spreadsheetControl1.CreateNewDocument();
                }
                else
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show("数据保存失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }


                //  txtProductItem_UcTextChanged(null, null);
                //解锁当前录入员的操作图纸
                // Common.GetLock.UnLock();
                //FLAG_CellValueChanged = true;//初始化尺寸值改变标识

            }
            catch(Exception ex)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);             
            }
        }
        //导入
        public override void DoImport(IButtonInfo sender)
        {
            #region 按钮权限
            if (!BuildSqlHelper.IsExistList("IE_Part_SizeParmList", "LamexMESManager")) return;
            #endregion
            DevExpress.XtraEditors.XtraMessageBox.Show("未上载数据中，图纸编号+部件编号重复时，原有记录将进行覆盖！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            OpenFileDialog dlg = new OpenFileDialog();
            DataTable dtEqual = new DataTable();//获取导入文件和数据库未上载标识 图纸号+部件号相同值
            List<string> Temp1 = new List<string>();
            List<string> Temp2 = new List<string>();
            StringBuilder sbFilter1 = new StringBuilder();
            StringBuilder sbFilter2 = new StringBuilder();
            if (DialogResult.OK == dlg.ShowDialog())
            {
                try
                {
                    SplashScreenTool.ShowSplashScreen(typeof(WaitForm1));
                    string path = dlg.FileName;
                    //导入EXCEL转换成Table
                    DataTable dtImport = Lamex.Common.ExecelHelper.ExcelToDataTable(path, 1);
                    if (dtImport.Rows.Count <= 0)
                    {
                        DevExpress.XtraEditors.XtraMessageBox.Show("导入数据为空！");
                        return;
                    }

                    //查询数据库表-上载标识不等于1
                    DataTable dt = BaseFactory<IE_Part_SizeParmList>.Instance.GetDataSet().Tables[0];

                    for (int i = 0; i < dtImport.Rows.Count; i++)
                    {

                        //找出未上载的重复值
                        for (int j = 0; j < dt.Rows.Count; j++)
                        {
                            if (dtImport.Rows[i]["图纸编号"].ToString() + dtImport.Rows[i]["部件编号"] ==
                                dt.Rows[j]["DWG_NO"].ToString() + dt.Rows[j]["PART_NO"].ToString())
                            {
                                Temp1.Add(dt.Rows[j]["DWG_NO"].ToString());
                                Temp2.Add(dt.Rows[j]["PART_NO"].ToString());
                            }
                        }
                    }
                    //删除上载标识不等于1的重复项
                    if (Temp1.Count > 0 && Temp2.Count > 0)
                    {
                        sbFilter1.AppendFormat(@" AND DWG_NO IN ('{0}')", string.Join("','", Temp1.ToArray()));
                        sbFilter2.AppendFormat(@" AND PART_NO IN ('{0}')", string.Join("','", Temp2.ToArray()));

                        string sqlDel = @"Delete  FROM IE_Part_SizeParmList where 1=1  " + sbFilter1 + " " + sbFilter2 + "  ";
                        int n = BaseBuildSql.ExecuteNonQuery(sqlDel);
                    }
                    //添加导入时间和导入用户
                    //添加两新列，其值为默认值
                    DataColumn dc1 = new DataColumn("UPDATE_TIME", typeof(DateTime));
                    dc1.DefaultValue = DateTime.Now;
                    dc1.AllowDBNull = false;//这在创建表的时候，起作用，在为已有表新增列的时候，不起作用
                    dtImport.Columns.Add(dc1);
                    DataColumn dc2 = new DataColumn("UPDATE_USER", typeof(string));
                    dc2.DefaultValue = Lamex.Common.Loginer.CurrentUser.AccountName;
                    dc2.AllowDBNull = false;
                    dtImport.Columns.Add(dc2);

                    //重新调整导入Table的列名
                    dtImport.Columns["图纸编号"].ColumnName = "DWG_NO";
                    dtImport.Columns["部件编号"].ColumnName = "PART_NO";
                    dtImport.Columns["阔度_1"].ColumnName = "WIDTH_1";
                    dtImport.Columns["阔度_2"].ColumnName = "WIDTH_2";
                    dtImport.Columns["深度_1"].ColumnName = "DEPTH_1";
                    dtImport.Columns["深度_2"].ColumnName = "DEPTH_2";
                    dtImport.Columns["厚度"].ColumnName = "THICK";
                    dtImport.Columns["长度"].ColumnName = "LENGTH";
                    dtImport.Columns["宽度"].ColumnName = "WIDTH";
                    dtImport.Columns["高度"].ColumnName = "HEIGHT";
                    dtImport.Columns["深度"].ColumnName = "DEPTH";

                    //批量插入数据库
                    if (BaseBuildSql.ExcuteInsert(dtImport, "IE_Part_SizeParmList"))
                    {
                        DevExpress.XtraEditors.XtraMessageBox.Show("数据导入成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    SplashScreenTool.CloseSplashScreen();

                }
                catch (Exception ex)
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message);
                }
            }

        }
        //导出-默认路径D:\MESTemp
        public override void DoExport(IButtonInfo sender)
        {
            if (string.IsNullOrEmpty(txtProductItem.Text))
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("请先选择产品类型！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            if (!Directory.Exists(@"D:\MESTemp"))
                Directory.CreateDirectory(@"D:\MESTemp");
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出Excel";
            fileDialog.InitialDirectory = @"D:\MESTemp";//导出默认路径
            fileDialog.Filter = "Excel文件(*.xlsx)|*.xlsx";//导出默认格式
            fileDialog.FileName = "MES_TEMP_" + DateTime.Now.ToString("yyMMdd_hhMMss");//导出默认名称
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                //导出表【IE_Part_TaskList】中当前产品类型，无“尺寸参数标识”的数据
                DataTable dtList = Common.GetDWG_List.Dwg_List("IE_Part_TaskList T LEFT jOIN IE_Part_MasterData M ON T.PART_NO=M.PART_NO",
                  " T.DWG_NO,T.PART_NO,M.CN_DESC,M.EN_DESC,T.REF_DWG",
                 " and ISNULL(T.PARM_FLAG,'')!='1' and ISNULL(T.UPLOAD_FLAG,'')!='1' and T.PROD_CATEGORY='" + txtProductItem.Text + "' ");


                GridControl grid = new GridControl();
                GridView view = new GridView();
                grid.ViewCollection.Add(view);
                grid.MainView = view;
                view.GridControl = grid;
                foreach (DataColumn dc in dtList.Columns)
                {
                    DevExpress.XtraGrid.Columns.GridColumn gridColumnNumber = view.Columns.AddVisible(dc.ColumnName);
                    gridColumnNumber.FieldName = dc.ColumnName;
                }
                grid.DataSource = dtList;
                this.Controls.Add(grid);
                grid.ForceInitialize();
                view.BestFitColumns();
                view.OptionsPrint.PrintHorzLines = false;
                view.OptionsPrint.PrintVertLines = false;
                view.ExportToXlsx(fileDialog.FileName);
                // DevExpress.XtraEditors.XtraMessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                if (DevExpress.XtraEditors.XtraMessageBox.Show("导出成功，是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                    System.Diagnostics.Process.Start(fileDialog.FileName);//打开指定路径下的文件

            }
        }
        //查询
        public override void DoView(IButtonInfo sender)
        {
            Init();
            GetSizeParmList();
           
        }

        public void GetSizeParmList()
        {
            //SplashScreenTool.ShowSplashScreen(typeof(WaitForm1));
            string sql_Pro = txtProductItem.Text.Length > 0 ? " and T.PROD_CATEGORY='" + txtProductItem.Text + "'" : " and T.PROD_CATEGORY like '%%'";
            string sql_DwgNo = txtDWG_NO.Text.Length > 0 ? " and T.DWG_NO='" + txtDWG_NO.Text + "'" : " and T.DWG_NO like '%%'";
            string sql_PartNo = txtPart_No.Text.Length > 0 ? " and T.PART_NO='" + txtPart_No.Text + "'" : " and T.PART_NO like '%%'";
            string sql_ImportTime = dateEdit1.Text.Length > 0 ? "and T.IMPORT_TIME>='" + dateEdit1.Text + "" + " 00:00:00' and T.IMPORT_TIME<='" + dateEdit1.Text + "" + " 23:59:59'" : "";
            string sql = sql_Pro + sql_DwgNo + sql_PartNo + sql_ImportTime;
            Worksheet sheet = this.spreadsheetControl1.ActiveWorksheet;
            dtList = new DataTable();
            //获取清单列表工艺路线标识不为1的数据
            dtList = Common.GetDWG_List.Dwg_List("IE_Part_TaskList T LEFT jOIN IE_Part_MasterData M ON T.PART_NO=M.PART_NO",
              "distinct T.DWG_NO,T.PART_NO,M.CN_DESC,M.EN_DESC,REF_DWG,REF_MAT,M.MODEL_NO",
              " " + sql + " and ISNULL(T.PARM_FLAG,'')!='1' and ISNULL(T.UPLOAD_FLAG,'')!='1' and M.MRP_CONTROLLER not in ('WG1','PG1') and ISNULL(T.LOCK_FLAG,'')!='1' ");
            //获取工艺路线表上载标识不为1的数据
            DataTable dtRouting = Common.GetDWG_List.Dwg_List(@"IE_Part_SizeParmList R JOIN IE_Part_TaskList T on R.DWG_NO=T.DWG_NO and R.PART_NO=T.PART_NO JOIN IE_Part_MasterData M ON M.PART_NO=T.PART_NO",
                "distinct T.DWG_NO,T.PART_NO,M.CN_DESC,M.EN_DESC,T.REF_DWG,REF_MAT,M.MODEL_NO",
                " " + sql + "  and ISNULL(T.PARM_FLAG,'')='1' and ISNULL(T.UPLOAD_FLAG,'')!='1' and M.MRP_CONTROLLER not in ('WG1','PG1') and ISNULL(T.LOCK_FLAG,'')!='1' ");
            if (txtDWG_NO.Text.Length > 0 || txtPart_No.Text.Length > 0)
            {
                //合并临时表
                dtList.Merge(dtRouting);
            }

            DataTable dtSizeList = Common.GetDWG_List.Dwg_List("IE_Part_SizeParmList T",
           "[DWG_NO],[PART_NO],[WIDTH_1],[WIDTH_2],[DEPTH_1],[DEPTH_2],[THICK],[LENGTH],[WIDTH],[HEIGHT],[DEPTH]",
           " " + sql_DwgNo + sql_PartNo + "  ");

            //若有重复项则去掉
            DataView dv = new DataView(dtList);
            DataTable dtResult = dv.ToTable(true, new[] { "DWG_NO", "PART_NO", "REF_DWG" });

            dtSizeCache = Common.GetTable.GetTable_SizeList_CH();//尺寸缓存表
         
            if (dtResult.Rows.Count > 0)
                for (int i = 0; i < dtResult.Rows.Count; i++)
                {
                    DataRow dr = dtSizeCache.NewRow();
                    dr["图纸编号"] = dtResult.Rows[i]["DWG_NO"].ToString();
                    dr["部件编号"] = dtResult.Rows[i]["PART_NO"].ToString();
                    dr["阔度_1"] = dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").Length > 0 ?
                        dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").CopyToDataTable().Rows[0]["WIDTH_1"].ToString() : "";
                    dr["阔度_2"] = dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").Length > 0 ?
                        dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").CopyToDataTable().Rows[0]["WIDTH_2"].ToString() : ""; 
                    dr["深度_1"] = dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").Length > 0 ?
                        dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").CopyToDataTable().Rows[0]["DEPTH_1"].ToString() : ""; 
                    dr["深度_2"] = dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").Length > 0 ?
                        dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").CopyToDataTable().Rows[0]["DEPTH_2"].ToString() : ""; 
                    dr["厚度"] = dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").Length > 0 ?
                        dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").CopyToDataTable().Rows[0]["THICK"].ToString() : ""; 
                    dr["长度"] = dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").Length > 0 ?
                        dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").CopyToDataTable().Rows[0]["LENGTH"].ToString() : ""; 
                    dr["宽度"] = dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").Length > 0 ?
                        dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").CopyToDataTable().Rows[0]["WIDTH"].ToString() : ""; 
                    dr["高度"] = dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").Length > 0 ?
                        dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").CopyToDataTable().Rows[0]["HEIGHT"].ToString() : ""; 
                    dr["深度"] = dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").Length > 0 ?
                       dtSizeList.Select("PART_NO='" + dtResult.Rows[i]["PART_NO"].ToString() + "'").CopyToDataTable().Rows[0]["DEPTH"].ToString() : ""; 
                    
                    dtSizeCache.Rows.Add(dr);
                }         
            sheet.Clear(spreadsheetControl1.Document.Range["A1:K" + CellRows + 1 + ""]);  //clear cell         
            sheet.Import(dtSizeCache, true, 0, 0);//import datatable
            CellRows = dtSizeCache.Rows.Count;
            //冻结首行
            sheet.FreezePanes(0, 0);
            //设置字体大小
            //sheet.Range["A1:K" + CellRows + 1 + ""].Font.Size = 9;
            //内容自适应
            sheet.Range["A1:K" + CellRows + 1 + ""].AutoFitColumns();
            sheet.Range["A1:K" + CellRows + 1 + ""].AutoFitRows();
            //SetExcelFormat();
            ////锁定前两列-但是表头不可拖动
            //sheet["$A:$XFD"].Protection.Locked = false; // lock the entire document
            //sheet["$A:$B"].Protection.Locked = true;
            //if (!sheet.IsProtected)
            //sheet.Protect("123456", WorksheetProtectionPermissions.Default);

           //// 设置字体格式
           // SpreadsheetFont cellFont = sheet.Range["A1:J" + CellRows + 1 + ""].Font;
           // // Set the font name.
           // cellFont.Name = "Tahoma";
           // // Set the font size.
           // cellFont.Size = 9;

            ////单元格区域格式-水平居中
            //Range range = sheet.Range["$A:$J"];
            //Formatting rangeFormatting = range.BeginUpdateFormatting();
            //rangeFormatting.Font.Size = 9;
            //rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            //range.EndUpdateFormatting(rangeFormatting);

            //数据有效性验证
            sheet.DataValidations.Add(sheet["C2:K" + CellRows + 1 + ""], DataValidationType.Decimal, DataValidationOperator.Between, 0.1, 10000);
            

            //设置列头颜色
            for (int i = 0; i < 11; i++)
            {
                sheet.Cells[0, i].FillColor = Color.LightGray;
            }
            //SplashScreenTool.CloseSplashScreen();
        }

        public void SetExcelFormat()
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            //设置字体格式
            SpreadsheetFont cellFont = sheet.Range["$A:$J"].Font;
            // Set the font name.
            cellFont.Name = "Tahoma";
            // Set the font size.
            cellFont.Size = 9;

            //单元格区域格式-水平居中
            Range range = sheet.Range["$A:$J"];
            Formatting rangeFormatting = range.BeginUpdateFormatting();
            rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            range.EndUpdateFormatting(rangeFormatting);
        }
        [DllImport("kernel32.dll")]
        private static extern bool SetProcessWorkingSetSize(IntPtr process, int minSize, int maxSize);
        //关闭
        private void Frm_IE_Part_SizeParmList_Excel_FormClosing(object sender, FormClosingEventArgs e)
        {
            //重新刷新内存
            GC.Collect();
            GC.WaitForPendingFinalizers();
            if (Environment.OSVersion.Platform == PlatformID.Win32NT)
            {
                SetProcessWorkingSetSize(Process.GetCurrentProcess().Handle, -1, -1);
            }
        }
    }
}
